package com.jinbei.common.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author zflu
 * Excel解析工具类
 * Created by lucky on 2018/7/23.
 */
public class ExcelUtil<T> {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private String className;

    public ExcelUtil(String className){
        this.className = className;
    }

    public List<T> getExcelInfo(MultipartFile file) {
        //获取文件名称
        String fileName = file.getOriginalFilename();
        List<T> result = null;
        try {
            if(!validateExcel(fileName)){
                return null;
            }
            boolean isExcel2003 = true;
            if(isExcel2007(fileName)){
                isExcel2003 = false;
            }
            result = createExcel(file.getInputStream(),isExcel2003);
        }catch (Exception e){
            logger.error("excel解析异常!<><><><> {}",e);
            e.printStackTrace();
        }
        return result;
    }

    private List<T> createExcel(InputStream is, boolean isExcel2003){
        Workbook wb = null;
        List<T> result = null;
        try {
            if(isExcel2003){
                wb = new HSSFWorkbook(is);
            }else{
                wb = new XSSFWorkbook(is);
            }
            result = readExcel(wb);
        }catch (Exception e){
            logger.error("excel解析错误 {}",e);
            e.printStackTrace();
        }
        return result;
    }

    private List<T> readExcel(Workbook wb) throws Exception{
        //得到第一个 sheet
        Sheet sheet = wb.getSheetAt(0);
        //获得sheet的总数
        int totalRows = sheet.getPhysicalNumberOfRows();
        int totalCells = 0;
        if(totalRows > 0 && sheet.getRow(0) != null){
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<T> list = new ArrayList<>();
        for (int i = 1 ; i < totalRows ;i++){
            Row row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            Class<?> bean = Class.forName(className);
            Object o = bean.newInstance();
            Field[] fields = bean.getDeclaredFields();
            for (int j = 0; j < totalCells ;j++){
                Cell cell = row.getCell(j);
                if(null != cell){
                    if(j > fields.length - 1){
                        continue;
                    }
                    fields[j].setAccessible(true);
                    if(cell.getCellTypeEnum().equals(CellType.NUMERIC) && j == 0){
                        String id = String.valueOf(cell.getNumericCellValue());
                        id = id.substring(0, id.length() - 2 > 0 ? id.length() - 2 : 1);
                        fields[j].set(o,new Integer(id));
                        continue;
                    }
                    if(cell.getCellTypeEnum().equals(CellType.STRING)){
                        fields[j].set(o,cell.getStringCellValue());
                        continue;
                    }
                    if(cell.getCellTypeEnum().equals(CellType.NUMERIC) && HSSFDateUtil.isCellDateFormatted(cell)){
                        int style = cell.getCellStyle().getDataFormat();
                        Date date = cell.getDateCellValue();
                        String value = null;
                        switch (style){
                            case 178:
                                value = new SimpleDateFormat("yyyy'年'M'月'd'日'").format(date);
                                break;
                            case 14:
                                value = new SimpleDateFormat("yyyy/MM/dd").format(date);
                                break;
                            case 179:
                                value = new SimpleDateFormat("yyyy/MM/dd HH:mm").format(date);
                                break;
                            case 181:
                                value = new SimpleDateFormat("yyyy/MM/dd HH:mm a ").format(date);
                                break;
                            case 22:
                                value = new SimpleDateFormat(" yyyy/MM/dd HH:mm:ss ").format(date);
                                break;
                            default:
                                break;
                        }
                        fields[j].set(o,value);
                        continue;
                    }
                    if(cell.getCellTypeEnum().equals(CellType.NUMERIC)){
                        fields[j].set(o,cell.getNumericCellValue());
                    }
                }
            }
            list.add((T) o);
        }
        return list;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return boolean
     */
    private static boolean validateExcel(String filePath) {
        if (StringUtils.isBlank(filePath)
                || !(isExcel2003(filePath)
                || isExcel2007(filePath))) {
            return false;
        }
        return true;
    }

    /**
     * 验证是否是 xls
     * @param filePath
     * @return
     */
    private static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 验证是否是 xlsx
     * @param filePath
     * @return
     */
    private static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}
